﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MixScript.BLL.FI
{
    public class cBalanceSheet : cBaseBLL
    {

        public DataTable ListOfData(String type)
        {
            String sSQL = @"SELECT subnum,subname  FROM accsubgroup g WHERE g.aplication = {0} ";
            // Seharusnya : FROM accgroup g WHERE g.aplication > 3 ";
            return Dbserver.ExecuteQuery(String.Format(sSQL, type), "IS");
        }
        public DataTable SubListOfData(String Groupnum)
        {
            String sSQL = @"SELECT * FROM accaccounts  where subnum = '{0}' ";
            return Dbserver.ExecuteQuery(String.Format(sSQL, Groupnum), "IS");
        }
        public Decimal getCurrent(String acctnum)
        {
            DataTable dt = new DataTable();
            String sSQL = @"SELECT a.acctnum AS acctnum,
                            ISNULL(SUM(CASE a.dbcd WHEN 'C' THEN 
	                                    CASE a.aplication WHEN  4 THEN l.valconvert 
	                                    WHEN 6 THEN  l.valconvert  ELSE 0 END
                                    WhEN 'D' THEN 
	                                    CASE a.aplication WHEN  5 THEN l.valconvert 
	                                    WHEN 7 THEN  l.valconvert  ELSE 0 END
                                    END),0)
                                    AS [current] 
                                    FROM accaccounts a LEFT JOIN accledger l ON l.acctnum=a.acctnum 
			                        --AND kd_cmpy='$id_company' AND period_id='$period_id'
                                    where a.acctnum = '{0}'
			                        GROUP BY a.acctnum";
            dt = Dbserver.ExecuteQuery(String.Format(sSQL, acctnum), "Current");

            if (dt.Rows.Count > 0)
                return (Decimal)dt.Rows[0].ItemArray[1];
            else
                return 0;
        }
        public Decimal getAccumulated(String acctnum)
        {
            DataTable dt = new DataTable();
            String sSQL = @"SELECT a.acctnum AS acctnum,
                            ISNULL(SUM(CASE a.dbcd WHEN 'C' THEN 
	                                    CASE a.aplication WHEN  4 THEN l.valconvert 
	                                    WHEN 6 THEN  l.valconvert  ELSE 0 END
                                    WhEN 'D' THEN 
	                                    CASE a.aplication WHEN  5 THEN l.valconvert 
	                                    WHEN 7 THEN  l.valconvert  ELSE 0 END
                                    END),0)
                                    AS [current] 
                                    FROM accaccounts a LEFT JOIN accledger l ON l.acctnum=a.acctnum 
			                        --AND kd_cmpy='$id_company' AND period_id='$period_id'
                                    where a.acctnum = '{0}'
			                        GROUP BY substr(a.acctnum,1,4) ";
            dt = Dbserver.ExecuteQuery(String.Format(sSQL, acctnum), "Current");

            if (dt.Rows.Count > 0)
                return (Decimal)dt.Rows[0].ItemArray[1];
            else
                return 0;
        }

    }
}
